package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.Integral;
import com.realtyManage.springboot.Model.Do.ProjectExhibition;
import com.realtyManage.springboot.Model.Do.User;
import io.lettuce.core.dynamic.annotation.Param;
import org.omg.CORBA.StringHolder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

/**
 * 积分
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-10-15
 */

@Repository
public interface IntegralRepository extends JpaRepository<Integral, Integer>, JpaSpecificationExecutor<Integral> {

    @Transactional
    @Modifying
    @Query("update Integral a set " +
            "a.integral = CASE WHEN :#{#integral.integral} IS NULL THEN a.integral ELSE :#{#integral.integral} END ," +
            "a.oneDateMaxIntegral = CASE WHEN :#{#integral.oneDateMaxIntegral} IS NULL THEN a.oneDateMaxIntegral ELSE :#{#integral.oneDateMaxIntegral} END " +
            "where a.id = :#{#integral.id}")
    int updateIntegral(@Param("integral") Integral integral);


    @Query(value = "select COUNT(1) znum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0\n" +
            "UNION ALL\n" +
            "select COUNT(1) anum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and `level`  = 'A'\n" +
            "UNION ALL\n" +
            "select COUNT(1) bnum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and `level`  = 'B'\n" +
            "UNION ALL\n" +
            "select COUNT(1) cnum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and `level`  = 'C'\n" +
            "UNION ALL\n" +
            "select COUNT(1) dnum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and `level`  = 'D'\n" +
            "UNION ALL\n" +
            "select COUNT(1) enum from customer_registration where is_system = 0 AND reception_id = 0 AND reception_name = 0 and `level`  = 'E'",nativeQuery = true)
    List getCountPublicCustomer();


    @Query(value = "SELECT * FROM `user` WHERE `level` = 2 AND status = 1",nativeQuery = true)
    List<User> getUserList();


    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND come_time LIKE %?2% and  reception_id = ?1",nativeQuery = true)
    int getReceptionNum(Integer id,String date);

    @Query(value = "SELECT reception_customer_num,expand_customer_num,invitation_num,return_visit_customer_num,identify_num,\n" +
            "deal_num,money_back_num,forward_num FROM user_task WHERE user_id = ?1 AND end_time LIKE %?2% and task_type = 0",nativeQuery = true)
    List<Object> getTask(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND come_time LIKE %?2% and  reception_id = ?1 AND expand_customer = 1",nativeQuery = true)
    int getTzNum(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND come_time LIKE %?2% and  reception_id = ?1  AND invitation = 1 AND visit = 1",nativeQuery = true)
    int getYyNum(Integer id, String date);

    @Query(value = "SELECT COUNT(1) num FROM(\n" +
            "(SELECT DISTINCT custom_id from customer_record WHERE custom_id IN(\n" +
            "SELECT id FROM customer_registration WHERE is_system = 0 AND come_time LIKE %?2% and  reception_id = ?1)) a)",nativeQuery = true)
    int getHfNum(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND identify = 1 AND reception_id =?1 AND come_time LIKE %?2%",nativeQuery = true)
    int getRcNum(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND effective = 1 AND reception_id =?1 AND come_time LIKE %?2%",nativeQuery = true)
    int getCjNum(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM customer_registration WHERE is_system = 0 AND money_back = 1 AND reception_id =?1 AND come_time LIKE %?2%",nativeQuery = true)
    int getHkNum(Integer id,String date);

    @Query(value = "SELECT COUNT(1) num FROM `forward` WHERE user_id =?1 AND forward_date LIKE %?2%",nativeQuery = true)
    int getZfNum(Integer id,String date);

    @Query(value = "SELECT reception_customer_num,expand_customer_num,invitation_num,return_visit_customer_num,identify_num,\n" +
            "deal_num,money_back_num,forward_num FROM user_task WHERE user_id like %?1% AND end_time LIKE %?2% AND task_type = 0 ORDER BY id DESC LIMIT 1",nativeQuery = true)
    List<String> getTaskE(Integer id,String date);


    @Query(value = "SELECT integral  FROM integral WHERE type = ?1",nativeQuery = true)
    int getIntegral(Integer type);

    @Query(value = "SELECT one_date_max_integral  FROM integral WHERE type = ?1",nativeQuery = true)
    int getMaxValue(Integer type);
}
